*global cloud "/Users/inga/Library/CloudStorage/OneDrive-NTNU/comtrade_monthly"
*cd /Users/inga/Desktop/jupi/

	clear all
	
	global main "/Users/ingahei/UiO Dropbox/IngaB Heiland/Ais project/Repository"
	*global data "C:\Users\ingahe\OneDrive - NTNU\jupi"
	global data "/Users/ingahei/UiO Dropbox/IngaB Heiland/Ais project/Repository/data_intermediate"
	global input "/Users/ingahei/UiO Dropbox/IngaB Heiland/Ais project/Repository/data_input"


	*global cloud2 "/Users/inga/UiO Dropbox/IngaB Heiland/comtrade_monthly"
	*global cloud "/Users/inga/UiO Dropbox/IngaB Heiland/comtrade"

	global year = 2019
	global cloud "/Users/ingahei/UiO Dropbox/IngaB Heiland/Desktop/comtrade_monthly/$year"
	global cloudU "/Users/ingahei/UiO Dropbox/IngaB Heiland/Desktop/comtrade_monthly"


	cd "$data"


local files : dir "$cloud" files "*.gz"
di `files'

global rlist `files'

foreach file in "$rlist" {

local fil = subinstr("`file'",".gz","",1)
local fi = subinstr("`file'",".gz",".txt",1) 

shell gunzip "$cloud/`file'" 
shell mv "$cloud/`fil'" "$cloud/`fi'"
import delim using "$cloud/`fi'", clear





*shell gunzip -k "$cloud/`file'" 
*shell mv "$cloud/`fil'" "$cloud/`fi'"
*shell mv "$cloud/`file'" "$cloud2/`file'"



capture noisily {  // a few files are empty
keep if partnercode==0 | partner2code==0
drop if partnercode==0 & partner2code==0
keep if flowcode=="M"
keep if customscode=="C00"
keep if motcode==0
drop if length(cmdcode)<6

replace cifvalue = fobvalue if cifvalue==. // Canada (and possibly others) report only in fob

keep period classificationcode cmdcode cifvalue reportercode partnercode partner2code
destring cmdcode, replace force

drop if partnercode==.	
sum reportercode
global reporter = r(max)
replace reporter = 381 if reporter==380 // Italy

if $reporter!=975 {
rename repor CountryCode
merge m:1 Co using "$input/countrycodes"
keep if _mer==3
drop _mer
rename ISO reporteriso
drop CountryCode
}

if $reporter==975 {
g reporteriso = "BTN"
drop reportercode
}

rename partnercod CountryCode
merge m:1 Co using "$input/countrycodes"
drop if _mer==2
drop _mer // for some reason, ITA has sometimes 380 and sometimes 381
replace ISO="ITA" if CountryCode==380
rename ISO partneriso
drop CountryCode

rename partner2cod CountryCode
merge m:1 Co using "$input/countrycodes"
drop if _mer==2
drop _mer // for some reason, ITA has sometimes 380 and sometimes 381
replace ISO="ITA" if CountryCode==380
rename ISO ndpartneriso
drop CountryCode

g cifvalue_co=cifvalue if partneriso=="WLD"
replace cifvalue = . if ndpartneriso!="WLD"



drop if ndpar=="N/A" | ndpartneriso=="EU2" | ndpartneriso==""
drop if par=="N/A" | partneriso=="EU2" | partneriso==""

replace partneris=ndpartneris if partneriso=="WLD"
replace ndpartneris=partneris if ndpartneriso=="WLD"




gcollapse (firstnm) cif*, by(period class partner ndpar cmd reporter)

count if classification=="H1"
if r(N) > 0 {
g hs02="" 
}

count if classification=="H2"
if r(N) > 0 {
g hs02 =cmd if classification=="H2"
}

count if classification=="H3"
if r(N) > 0 {
g hs2007 =cmd if classification=="H3"
tostring hs2007, replace
replace hs2007 = "0"+hs2007 if length(hs2007)==5
rename hs2007 hs07
merge m:1 hs07  using "$input/hs07tohs02", update
drop if _me==2
drop _m hs07
}

count if classification=="H4"
if r(N) > 0 {
g hs2012 =cmd if classification=="H4"
tostring hs2012, replace
replace hs2012 = "0"+hs2012 if length(hs2012)==5
rename hs2012 hs12
merge m:1 hs12 using "$input/hs12tohs02", update
drop if _me==2
drop _m hs12
}
 
count if classification=="H5"
if r(N) > 0 {
g hs2017 =cmd if classification=="H5"
rename hs2017 HS
merge m:1 HS  using "$input/HS17toHS02", update
drop if _me==2
drop _m
tostring HS2002, replace
replace HS2002 = "0"+HS2002 if length(HS2002)==5
rename HS2002 hs02 
}

destring hs02, replace


compress
sum period
global period = r(mean)


g d2=floor(hs02/10000)
*gcollapse (firstnm) cif*, by(period partner d2 reporter)

gcollapse (sum)  cif*, by(period partner d2 reporter)


save "$cloud/comtrade_ct_${reporter}_${period}_2dHS2", replace
}
global rlist "`ferest()'" // the list of unprocessed files
erase "$cloud/`fi'"
}



	forvalues y=2013(1)2019 {	
	clear all
	global year = `y'
	
	local files : dir "$cloudU/$year" files "*_2dHS2.dta"
	tempvar p
	
	foreach file in `files' {
append using "$cloudU/$year/`file'"
	erase "$cloudU/$year/`file'"
	}
	save "$cloudU/allHS2_$year", replace	
}


tempvar p
u "$cloudU/allHS2_2013", clear
forvalues y=2016(1)2019 {		
append using "${cloudU}/allHS2_`y'"
*erase "${cloudU}/allHS2_`y'.dta"
}
save "$data/allHS2_2d", replace



*** total bilateral trade, quarterly
u "$data/allHS2_2d", clear
g cifvalue_nr=cifvalue if d2<25 | (d2>27 & d2<72) | d2>81            // values excluding raw materials
g cifvalue_co_nr=cifvalue_co if d2<25 | (d2>27 & d2<72) | d2>81      // values excluding raw materials
collapse (sum) cif*, by(repor partner period) // collapse over sectors

rename reporteriso iso_j
rename partneriso iso_i
egen CO = total(cifvalue_co), by(period iso_j)
egen VA = total(cifvalue), by(period iso_j)
replace cifvalue=. if VA==0  // replace zeros with missing if reporter did not report anything in this period
replace cifvalue_nr=. if VA==0
replace cifvalue_co=. if CO==0
replace cifvalue_co_nr=. if CO==0

rename cifvalue* value*
tostring period, replace
g year = substr(perio,1,4)
g month = substr(perio,5,2)
destring year month, replace
g q=ceil(month/3)

gcollapse (sum) val* (count) nmvalue = value nmvalue_co = value_co nmvalue_nr = value_nr nmvalue_co_nr = value_co_nr, by(year q iso_i iso_j) fast // collapse from monthly to quarterly
replace value=. if nmvalue!=3.         // keep only quarters where all three months are reported
replace value_co=. if nmvalue_co!=3
replace value_co_nr=. if nmvalue_co_nr!=3
replace value_nr=. if nmvalue_nr!=3
replace value_co=value if value_co==.  // replace missing imports by country of consignment with imports by country of origin if available
replace value_co_nr=value_nr if value_co_nr==.
g iso_o = iso_i 
g iso_d = iso_j
replace iso_o="ROM" if iso_o=="ROU"
replace iso_d="ROM" if iso_d=="ROU"
replace iso_o="ZAR" if iso_o=="COD"
replace iso_d="ZAR" if iso_d=="COD"
replace iso_o="TMP" if iso_o=="TLS"
replace iso_d="TMP" if iso_d=="TLS"
replace iso_o="YUG" if iso_o=="SRB"
replace iso_d="YUG" if iso_d=="SRB"
replace iso_o="YUG" if iso_o=="MNE"
replace iso_d="YUG" if iso_d=="MNE"
replace iso_o="PAL" if iso_o=="PSE"
replace iso_d="PAL" if iso_d=="PSE"
save "$data/all_q", replace





*** total bilateral trade, monthly
u "$data/allHS2_2d", clear
collapse (sum) cif*, by(repor partner period) // collapse over sectors
rename reporteriso iso_j
rename partneriso iso_i
egen CO = total(cifvalue_co), by(period iso_j)
egen VA = total(cifvalue), by(period iso_j)
replace cifvalue=. if VA==0 // replace zeros with missing if reporter did not report anything in this period
replace cifvalue_co=. if CO==0
rename cifvalue value
rename cifvalue_co value_co
replace value_co=value if value_co==.  // replace missing imports by country of consignment with imports by country of origin if available
g iso_o = iso_i 
g iso_d = iso_j
replace iso_o="ROM" if iso_o=="ROU"
replace iso_d="ROM" if iso_d=="ROU"
replace iso_o="ZAR" if iso_o=="COD"
replace iso_d="ZAR" if iso_d=="COD"
replace iso_o="TMP" if iso_o=="TLS"
replace iso_d="TMP" if iso_d=="TLS"
replace iso_o="YUG" if iso_o=="SRB"
replace iso_d="YUG" if iso_d=="SRB"
replace iso_o="YUG" if iso_o=="MNE"
replace iso_d="YUG" if iso_d=="MNE"
replace iso_o="PAL" if iso_o=="PSE"
replace iso_d="PAL" if iso_d=="PSE"
tostring period, replace
g year = substr(perio,1,4)
g month = substr(perio,5,2)
destring year month period, replace
g q=ceil(month/3)
save "$data/all_m", replace



